package spark

import java.sql.DriverManager
import java.sql.PreparedStatement
import java.sql.Connection
import org.apache.spark.SparkConf
import org.apache.spark.streaming.kafka010._
import org.apache.spark.streaming.{Seconds, StreamingContext}
import scala.collection.mutable

object OrderValidInvalidCount2 {
  def main(args: Array[String]): Unit = {
// 测试
    val conf = new SparkConf().setAppName("OrderValidInvalidCount").setMaster("local[*]")
    val ssc = new StreamingContext(conf, Seconds(8))
    ssc.sparkContext.setLogLevel("WARN")

    val kafkaParams = Map(
      "bootstrap.servers" -> "192.168.136.128:9092",
      "key.deserializer" -> "org.apache.kafka.common.serialization.StringDeserializer",
      "value.deserializer" -> "org.apache.kafka.common.serialization.StringDeserializer",
      "group.id" -> "niit",
      "auto.offset.reset" -> "earliest"
    )

    val stream = KafkaUtils.createDirectStream[String, String](
      ssc,
      LocationStrategies.PreferConsistent,
      ConsumerStrategies.Subscribe[String, String](Array("orders"), kafkaParams)
    )

    // 生成(product, isValid)组合键，统计每个批次内的出现次数
    val productValidPairs = stream.map { record =>
      val fields = record.value().split("\t")
      val product = fields(1)  // 产品名称在第2个字段
      val isValid = fields(6)  // isValid在第6个字段
      val isValidBoolean = isValid match {
        case "Y" => true
        case "N" => false
        case _ => false  // 默认无效
      }
      ((product, isValidBoolean), 1L)  // 组合键：(产品, 是否有效)
    }

    // 按组合键统计每个批次内的数量
    val countStream = productValidPairs.reduceByKey(_ + _)
    countStream.print()
    // 将结果写入MySQL（每个批次的统计结果独立写入）
    countStream.foreachRDD { rdd =>
      if (!rdd.isEmpty()) {
        rdd.foreachPartition { partition =>
          var conn: Connection = null
          var stmt: PreparedStatement = null
          try {
            // 创建数据库连接
            val url = "jdbc:mysql://localhost:3306/huel2?useSSL=false&serverTimezone=UTC"
            val user = "root"
            val password = "123456"
            conn = DriverManager.getConnection(url, user, password)

            // 批量插入当前批次的数据
            conn.setAutoCommit(false)
            partition.foreach { case ((product, isValid), count) =>
              val sql = "INSERT INTO task2 (product, is_valid, count) VALUES (?,?,?)"
              stmt = conn.prepareStatement(sql)
              stmt.setString(1, product)
              stmt.setBoolean(2, isValid)
              stmt.setLong(3, count)  // 直接使用当前批次统计的数量，不累计
              stmt.executeUpdate()
              stmt.close()
            }
            conn.commit()
          } catch {
            case e: Exception =>
              e.printStackTrace()
              if (conn != null) conn.rollback()
          } finally {
            if (stmt != null) try { stmt.close() } catch { case e: Exception => e.printStackTrace() }
            if (conn != null) try { conn.close() } catch { case e: Exception => e.printStackTrace() }
          }
        }
      }
    }

    ssc.start()
    ssc.awaitTermination()
  }
}